﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace BluSteamServer.Model.DataAccessLayer
{
    public class DataAccessObject
    {
        
        //private const string _connectionString = @"Data Source=DREWANOBRIEA6AF\SQLEXPRESS;Initial Catalog=BluSteamData;Integrated Security=True";
        //private const string _connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\BluSteamServerDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        private string _connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\Projects\BluSteamServerProject\BluSteamServer.Database\BluSteamServerDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        private const string _testString = @"Server=.\SQLExpress; Data Source=C:\Projects\BluSteamServerProject\BluSteamServer.Database\BluSteamDatabase.sdf; Trusted_Connection=Yes";
        //private string _connectionString;

        public DataAccessObject()
        {
            //this.GetDatabasePath();
        }

        private void GetDatabasePath()
        {
            string path = Environment.CurrentDirectory;
            path = path.Replace("\\TestResults", "");

            int index = path.LastIndexOf('\\');

            path = path.Substring(0, index);
            index = path.LastIndexOf('\\');
            path = path.Substring(0, index);
            path += @"\BluSteamServer.Database\BluSteamServerDatabase.mdf";

            _connectionString = 
                string.Format(@"Data Source=.\SQLEXPRESS;AttachDbFilename={0};Integrated Security=True;Connect Timeout=30;User Instance=True",
                path);
        }

        public void TestConnection()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_testString))
                {
                    connection.Open();
                }
            }
            catch (SqlException ex)
            {
                System.Diagnostics.Debug.WriteLine("Find error: " + ex);
            }
        }

        public void InsertIntoTable(string table, Dictionary<string, object> data)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    // build sql command string
                    string sql = string.Format("INSERT INTO {0} VALUES(", table);

                    List<string> attributeNames = new List<string>(data.Keys);
                    for (int index = 0; index < attributeNames.Count; index++)
                    {
                        // final attribute name requires a closing bracket
                        if (index == attributeNames.Count - 1)
                        {
                            sql += string.Format("@{0})", attributeNames[index]);
                            break;
                        }
                        sql += string.Format("@{0},", attributeNames[index]);
                    }

                    System.Diagnostics.Debug.WriteLine("sql string: " + sql);

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        // add SqlParameters using values from attributes
                        foreach (KeyValuePair<string, object> attribute in data)
                        {
                            command.Parameters.Add(new SqlParameter(attribute.Key, attribute.Value.ToString()));
                        }
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                System.Diagnostics.Debug.WriteLine("Find error: " + ex);
            }
        }

        public List<Dictionary<string, object>> GetAllFromTable(string table)
        {
            List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();

            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    string sql = string.Format("SELECT * FROM {0}", table);
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Dictionary<string, object> data = new Dictionary<string, object>();
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    data.Add(reader.GetName(index), reader.GetValue(index));
                                }
                                results.Add(data);
                            }
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                System.Diagnostics.Debug.WriteLine("Find error: " + ex);
            }
            return results;
        }

        public Dictionary<string, object> GetRecordByPK(string table, string pk)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    string sql = string.Format("SELECT * FROM {0} WHERE id = {1}", table, pk);
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    result.Add(reader.GetName(index), reader.GetValue(index));
                                }
                            }
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                System.Diagnostics.Debug.WriteLine("Find error: " + ex);
            }
            return result;
        }

        public Dictionary<string, object> GetRecordByAttribute(string table, string attribute, string value)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            try
            {
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    string sql = string.Format("SELECT * FROM {0} WHERE {1} = '{2}'", table, attribute, value);
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                for (int index = 0; index < reader.FieldCount; index++)
                                {
                                    result.Add(reader.GetName(index), reader.GetValue(index));
                                }
                            }
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                System.Diagnostics.Debug.WriteLine("Find error: " + ex);
            }
            return result;
        }
    }
}

